package com.taekwon.remoteservice;
import jxl.Sheet;
import jxl.Workbook;
import java.io.File;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import java.util.ArrayList;
import ibatis.MySqlMapper;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.taekwon.model.Wordlingoes;
public class WordlingoesDao {
	private static SqlMapClient sqlMapClient;
	private static Logger logger = Logger.getLogger(WordlingoesDao.class);
	public WordlingoesDao() {
		sqlMapClient = MySqlMapper.getSqlMapClient();
	}
	public ArrayList selectTop(Wordlingoes wordlingoes) throws SQLException{
		logger.debug("start");
		logger.debug("wordlingoes:"+wordlingoes);
		ArrayList arraylist = new ArrayList();
		try{
			sqlMapClient.startTransaction(); 
			arraylist = (ArrayList)sqlMapClient.queryForList("WordlingoesDao.selectTop",wordlingoes); 
			sqlMapClient.commitTransaction(); 
		}catch(Exception e){
			logger.debug("error:"+e);
		}finally{
			sqlMapClient.endTransaction();
		}
		logger.debug("end");
		return arraylist;
	}
	public ArrayList selectWordsWithKeyword(Wordlingoes wordlingoes) throws SQLException{
		logger.debug("start");
		logger.debug("wordlingoes:"+wordlingoes);
		ArrayList arraylist = new ArrayList();
		try{
			sqlMapClient.startTransaction(); 
			arraylist = (ArrayList)sqlMapClient.queryForList("WordlingoesDao.selectWordsWithKeyword",wordlingoes); 
			sqlMapClient.commitTransaction(); 
		}catch(Exception e){
			logger.debug("error:"+e);
		}finally{
			sqlMapClient.endTransaction();
		}
		logger.debug("end");
		return arraylist;
	}
	public int deleteWord(Wordlingoes wordlingoes) throws SQLException{
		logger.debug("start");
		logger.debug("wordlingoes:"+wordlingoes);
		int int_ = 0;
		try{
			sqlMapClient.startTransaction(); 
			int_ = (int)sqlMapClient.delete("WordlingoesDao.deleteWord",wordlingoes); 
			sqlMapClient.commitTransaction(); 
		}catch(Exception e){
			logger.debug("error:"+e);
		}finally{
			sqlMapClient.endTransaction();
		}
		logger.debug("end");
		return int_;
	}
	public int updateWord(Wordlingoes wordlingoes) throws SQLException{
		logger.debug("start");
		logger.debug("wordlingoes:"+wordlingoes);
		int int_ = 0;
		try{
			sqlMapClient.startTransaction(); 
			int_ = (int)sqlMapClient.update("WordlingoesDao.updateWord",wordlingoes); 
			sqlMapClient.commitTransaction(); 
		}catch(Exception e){
			logger.debug("error:"+e);
		}finally{
			sqlMapClient.endTransaction();
		}
		logger.debug("end");
		return int_;
	}
	public int insertWord(Wordlingoes wordlingoes) throws SQLException{
		logger.debug("start");
		logger.debug("wordlingoes:"+wordlingoes);
		int int_ = 0;
		try{
			sqlMapClient.startTransaction(); 
			int_ = (int)sqlMapClient.update("WordlingoesDao.insertWord",wordlingoes); 
			sqlMapClient.commitTransaction(); 
		}catch(Exception e){
			logger.debug("error:"+e);
		}finally{
			sqlMapClient.endTransaction();
		}
		logger.debug("end");
		return int_;
	}
	public static void main(String[] args) throws SQLException {
		WordlingoesDao wordlingoesdao = new WordlingoesDao();
	}
	public void insertDataes() throws Exception{
		String fullText = "";
		String dirSource = "링고스.xls";
		File dir = new File("E:/work/workEnglish");
		File fileInput = new File(dir, dirSource);

		if (!fileInput.exists()) {
			logger.debug("file not found");
		}

		if (!fileInput.canRead()) {
			logger.debug("can't read file");
		}

		Workbook wbInput = Workbook.getWorkbook(fileInput);

		if (wbInput == null) {
			logger.debug("Workbook is null!!");
		}
		Sheet sheet = wbInput.getSheet("word2");
		Wordlingoes wordlingoes = new Wordlingoes();
		for(int i=1;i<10834;i++){
			try {
				wordlingoes = new Wordlingoes();
				wordlingoes.setEng(sheet.getCell(0,i).getContents().trim());
				wordlingoes.setKor(sheet.getCell(1,i).getContents().trim());
				wordlingoes.setJpn(sheet.getCell(2,i).getContents().trim());
				wordlingoes.setChn(sheet.getCell(3,i).getContents().trim());
				wordlingoes.setGer(sheet.getCell(4,i).getContents().trim());
				insertWord(wordlingoes);
				logger.debug("i:"+i);

			} catch (Exception e) {
				logger.debug("e:"+e);
				continue;
			}

		}
	}	
}
